package com.eorchis.module.trainingquestionnaire.ui.controller;

import java.io.PrintWriter;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;

import com.eorchis.core.service.IBaseService;
import com.eorchis.core.springext.mvc.SessionConstant;
import com.eorchis.core.ui.commond.ResultState;
import com.eorchis.core.ui.controller.AbstractBaseController;
import com.eorchis.module.department.domain.Department;
import com.eorchis.module.trainingneeds.domain.QueryQuestionnaireBean;
import com.eorchis.module.trainingquestionnaire.domain.ClassQuestionnaireSummary;
import com.eorchis.module.trainingquestionnaire.domain.PreviewQuestionnaireBean;
import com.eorchis.module.trainingquestionnaire.domain.QuestionnaireEntity;
import com.eorchis.module.trainingquestionnaire.domain.QuestionnaireQueryBean;
import com.eorchis.module.trainingquestionnaire.service.IQuestionnaireService;
import com.eorchis.module.trainingquestionnaire.ui.commond.QuestionnaireQueryCommond;
import com.eorchis.module.trainingquestionnaire.ui.commond.QuestionnaireValidCommond;
import com.eorchis.module.user.domain.User;
import com.eorchis.utils.utils.PropertyUtil;

/**
 * Title: QuestionnaireController<br>
 * Description: 调查问卷管理控制器<br>
 * Company: GoldGov<br>
 * Copyright @ 2012 GoldGov .All rights reserved.<br>
 * @author Administrator
 * @createDate 2014-08-27
 * @version $Revision$
 */
@Controller("trainingQuestionnaireController")
@RequestMapping(value = QuestionnaireController.MODULE_PATH)
public class QuestionnaireController  extends AbstractBaseController<QuestionnaireValidCommond,QuestionnaireQueryCommond>
{
	// 模块页面根路径
	public static final String MODULE_PATH = "/module/trainingQuestionnaire";
	// 调查问卷接口
	@Autowired
	@Qualifier("main.java.com.eorchis.module.trainingquestionnaire.service.impl.QuestionnaireServiceImpl")
	private IQuestionnaireService questionnaireService;
	
	public IBaseService getService() {
		return questionnaireService;
	}

	public String getModulePath() {
		return MODULE_PATH;
	}
	
	public String getPageBasePath() {
		return "/portal/project/module/trainingQuestionnaire";
	}
	
	/**
	 * 预修改问卷
	 * @param validCommond
	 * @return
	 * @throws Exception
	 * @author Xupp
	 * @createDate 2014-8-27
	 */
	@RequestMapping(value="/preUpdateQuestionnaire")
	public String preUpdateQuestionnaire(@ModelAttribute("result") QuestionnaireValidCommond commond,
			HttpServletRequest request, HttpServletResponse response,
			@ModelAttribute("resultState") ResultState resultState)throws Exception{
		this.find(commond, request, response, resultState);
		User user=new User();
		commond.setUser(user);
		resultState.setState(ResultState.RESULT_STATE_SUCCESS);
		resultState.setMessage("查询成功！");
		return "";
	}
	/**
	 * 修改问卷
	 * @param validCommond
	 * @return
	 * @throws Exception
	 * @author Xupp
	 * @createDate 2014-8-27
	 */
	@RequestMapping(value="/updateTrainingQuestionnaire")
	public String updateQuestionnaire(@ModelAttribute("resultList") QuestionnaireValidCommond commond,
			HttpServletRequest request, HttpServletResponse response,
			@ModelAttribute("resultState") ResultState resultState)throws Exception{
		if(token.isTokenValid(request, true)){//判断token
		try {
			QuestionnaireValidCommond vcommond = new QuestionnaireValidCommond();
			vcommond=this.getService().find(commond.getQuestionnaireId());
			//把值转换给查询出来的对象
			vcommond.setBeginDate(commond.getBeginDate());
			vcommond.setEndDate(commond.getEndDate());
			vcommond.setQuestionnaireExplain(commond.getQuestionnaireExplain());
			vcommond.setQuestionnaireName(commond.getQuestionnaireName());
			vcommond.setQuestionnaireType(commond.getQuestionnaireType());
			vcommond.setSurveyType(commond.getSurveyType());
			questionnaireService.update(vcommond);
		} catch (Exception e) {
			e.printStackTrace();
			resultState.setMessage("系统错误!");
			resultState.setState(ResultState.RESULT_STATE_FAIL);
		}
		resultState.setMessage("修改成功!");
		resultState.setState(ResultState.RESULT_STATE_SUCCESS);
		}else{
			resultState.setState(ResultState.RESULT_STATE_REPEAT_SUBMIT);//重复提交
			resultState.setMessage("重复提交");
		}
		return "";
	}
	/**
	 * 作废问卷
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Xupp
	 * @createDate 2014-8-27
	 */
	@RequestMapping(value="/cancelQuestionnaire")
	public String deleteQuestionnaire(@ModelAttribute("resultList") QuestionnaireQueryCommond commond,
			HttpServletRequest request, HttpServletResponse response,
			@ModelAttribute("resultState") ResultState resultState)throws Exception{
		try {
			int count=0;
			if(!"".equals(commond.getSearchQuestionnaireIds())&&commond.getSearchQuestionnaireIds()!=null){
				commond.setActiveState(QuestionnaireQueryCommond.IS_CANCEL);
				count= questionnaireService.updateQuestionnaireActiveState(commond);
			}
			if(count>0){
				resultState.setState(ResultState.RESULT_STATE_SUCCESS);
				resultState.setMessage("作废成功！");
			}else{
				resultState.setState(ResultState.RESULT_STATE_FAIL);
				resultState.setMessage("系统错误，请联系管理员!");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}
	/**
	 * 启用问卷
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Xupp
	 * @createDate 2014-8-27
	 */
	@RequestMapping(value="/enableQuestionnaire")
	public String eQuestionnaire(@ModelAttribute("resultList") QuestionnaireQueryCommond commond,
			HttpServletRequest request, HttpServletResponse response,
			@ModelAttribute("resultState") ResultState resultState)throws Exception{
		if(token.isTokenValid(request, true)){//判断token
		int count=0;
		if(!"".equals(commond.getSearchQuestionnaireIds())&&commond.getSearchQuestionnaireIds()!=null){
			commond.setActiveState(QuestionnaireQueryCommond.IS_ENABLE);
			count= questionnaireService.updateQuestionnaireActiveState(commond);
		}
		if(count>0){
			resultState.setState(ResultState.RESULT_STATE_SUCCESS);
			resultState.setMessage("启用成功！");
		}else{
			resultState.setState(ResultState.RESULT_STATE_FAIL);
			resultState.setMessage("系统错误，请联系管理员!");
		}
		}else{
			resultState.setState(ResultState.RESULT_STATE_REPEAT_SUBMIT);//重复提交
			resultState.setMessage("重复提交");
		}
		return "";
	}
	/**
	 * 新增问卷
	 * @param validCommond
	 * @return
	 * @throws Exception
	 * @author Xupp
	 * @createDate 2014-8-27
	 */
	@RequestMapping(value="/addQuestionnaire")
	public String addQuestionnaire(@ModelAttribute("result")QuestionnaireValidCommond commond,
			HttpServletRequest request,HttpServletResponse response,
			@ModelAttribute("resultState")ResultState resultState)throws Exception{
		if(token.isTokenValid(request, true)){//判断token
		if(commond.getQuestionnaireExplain().length()<200){
			commond.setActiveState(QuestionnaireValidCommond.IS_ENABLE);
			User user=(User)request.getSession().getAttribute(SessionConstant.USER_INFO);
			commond.setUser(user);
			Department department = (Department) request.getSession()
					.getAttribute(SessionConstant.CURRENT_DEPARTMENT);
			commond.setDepartment(department);
			commond.setQuestionnaireType(commond.getQuestionnaireType());
			commond.setPublishState(QuestionnaireEntity.IS_PUBLISH_N);
			commond.setCreateDate(new Date());
			questionnaireService.save(commond);
			resultState.setState(ResultState.RESULT_STATE_SUCCESS);
			resultState.setMessage("添加成功！");
		}else{
			resultState.setState(ResultState.RESULT_STATE_FAIL);
			resultState.setMessage("问卷说明不许超过200个字符！");
		}
		}else{
			resultState.setState(ResultState.RESULT_STATE_REPEAT_SUBMIT);//重复提交
			resultState.setMessage("重复提交");
		}
		return "";
	}
	/**
	 * 查询活动问卷列表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Xupp
	 * @createDate 2014-8-27
	 */
	@RequestMapping(value="/listQuestionnaire")
	public String listQuestionnaire(@ModelAttribute("resultList")QuestionnaireQueryCommond commond,
			HttpServletRequest request,HttpServletResponse response,
			@ModelAttribute("resultState")ResultState resultState)throws Exception{
		commond.setActiveState(QuestionnaireQueryCommond.IS_ENABLE);
		commond.setResultList(questionnaireService.queryListByActiveState(commond));
		resultState.setState(ResultState.RESULT_STATE_SUCCESS);
		resultState.setMessage("");
		return "";
	}
	/**
	 * 发布撤销培训需求调查问卷
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-7
	 */
	@RequestMapping(value = "/publishQuestionnaire")
	public String publishQuestionnaire(
			@ModelAttribute("resultList") QuestionnaireQueryCommond queryCommond,
			HttpServletRequest request, HttpServletResponse response,
			@ModelAttribute("resultState") ResultState resultState)
					throws Exception
	{
		resultState.setState(ResultState.RESULT_STATE_FAIL);
		String mess=questionnaireService.publishQuestionnaire(queryCommond);
		resultState.setMessage(mess);
		if("操作成功".equals(mess))
		{
			/*if(queryCommond.getPublishState().intValue()==QuestionnaireEntity.IS_PUBLISH_Y.intValue())
			{
				String msg=questionnaireService.sendStartMessInPublish(queryCommond);
			}*/
			resultState.setState(ResultState.RESULT_STATE_SUCCESS);
		}
		return "";
	}
	/**
	 * 预览问卷
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-5-28
	 */
	@RequestMapping(value = "/previewQuestionnaire")
	public String previewQuestionnaire(
			@ModelAttribute("result") QuestionnaireValidCommond commond,
			HttpServletRequest request, HttpServletResponse response,
			@ModelAttribute("resultState") ResultState resultState)
					throws Exception
	{
		PreviewQuestionnaireBean previewQuestionnaireBean=questionnaireService.previewQuestionnaire(commond);
		
		commond.setPreviewQuestionnaireBean(previewQuestionnaireBean);
		resultState.setState(ResultState.RESULT_STATE_SUCCESS);
		
		return "";
	}
	/**
	 * 统计分析，问卷列表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-4-29
	 */
	@RequestMapping(value = "/findEndQuestionnaireList")
	public String findEndQuestionnaireList(
			@ModelAttribute("resultList") QuestionnaireQueryCommond queryCommond,
			HttpServletRequest request, HttpServletResponse response,
			@ModelAttribute("resultState") ResultState resultState)
					throws Exception
	{
		List<QueryQuestionnaireBean> list=questionnaireService.findEndQuestionnaireList(queryCommond);
		queryCommond.setResultList(list);
		return "";
	}
	

	/**
	 * 配置问卷列表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-4-29
	 */
	@RequestMapping(value = "/configureQuestionnaire")
	public String configureQuestionnaire(
			@ModelAttribute("resultList") QuestionnaireQueryCommond queryCommond,
			HttpServletRequest request, HttpServletResponse response,
			@ModelAttribute("resultState") ResultState resultState)
					throws Exception
	{
		List<QuestionnaireQueryBean> list=questionnaireService.configureQuestionnaire(queryCommond);
		queryCommond.setResultList(list);
		return "";
	}
	
	/**
	 * 配置课程问卷列表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-4-29
	 */
	@RequestMapping(value = "/configureCourseQuestionnaire")
	public String configureCourseQuestionnaire(
			@ModelAttribute("resultList") QuestionnaireQueryCommond queryCommond,
			HttpServletRequest request, HttpServletResponse response,
			@ModelAttribute("resultState") ResultState resultState)
					throws Exception
	{
		List<QuestionnaireQueryBean> list=questionnaireService.configureCourseQuestionnaire(queryCommond);
		queryCommond.setResultList(list);
		return "";
	}
	
	/**
	 * @查询评估开始和结束时间
	  * @Name: assessTime
	  * @Description: @param queryCommond
	  * @Description: @param request
	  * @Description: @param response
	  * @Description: @param resultState
	  * @Description: @return
	  * @Description: @throws Exception
	  * @Author: GUOLEI
	  * @Version: V1.00
	  * @Create Date: 2016-2-5下午2:19:45
	  * @Parameters: QuestionnaireController
	  * @Return: String
	 */
	@RequestMapping(value = "/assessTime")
	public String assessTime(
			@ModelAttribute("resultList") QuestionnaireValidCommond validcommond,
			HttpServletRequest request, HttpServletResponse response,
			@ModelAttribute("resultState") ResultState resultState)
					throws Exception
	{
		QuestionnaireQueryBean questionnaireQuery =questionnaireService.assessTime(validcommond);
		if(PropertyUtil.objectNotEmpty(questionnaireQuery)){
			validcommond.setAssessStartDay(questionnaireQuery.getAssessStartDay());
			validcommond.setAssessEndDay(questionnaireQuery.getAssessEndDay());
			//resultState.setMessage("查询成功！");
			resultState.setState(ResultState.RESULT_STATE_SUCCESS);
		}
		
		return "";
	}
	
	/**
	 * @查询评估开始和结束时间
	  * @Name: assessTime
	  * @Description: @param queryCommond
	  * @Description: @param request
	  * @Description: @param response
	  * @Description: @param resultState
	  * @Description: @return
	  * @Description: @throws Exception
	  * @Author: GUOLEI
	  * @Version: V1.00
	  * @Create Date: 2016-2-5下午2:19:45
	  * @Parameters: QuestionnaireController
	  * @Return: String
	 */
	@RequestMapping(value = "/assessCourseTime")
	public String assessCourseTime(
			@ModelAttribute("resultList") QuestionnaireValidCommond validcommond,
			HttpServletRequest request, HttpServletResponse response,
			@ModelAttribute("resultState") ResultState resultState)
					throws Exception
	{
		QuestionnaireQueryBean questionnaireQuery =questionnaireService.assessCourseTime(validcommond);
		if(PropertyUtil.objectNotEmpty(questionnaireQuery)){
			validcommond.setAssessCourseStartDay(questionnaireQuery.getAssessCourseStartDay());
			validcommond.setAssessCourseEndDay(questionnaireQuery.getAssessCourseEndDay());
			resultState.setMessage("查询成功！");
			resultState.setState(ResultState.RESULT_STATE_SUCCESS);
		}
		
		return "";
	}
	
	/**
	 * @更新评估开始时间和评估结束时间
	  * @Name: updateAssessTime
	  * @Description: @param validcommond
	  * @Description: @param request
	  * @Description: @param response
	  * @Description: @param resultState
	  * @Description: @return
	  * @Description: @throws Exception
	  * @Author: GUOLEI
	  * @Version: V1.00
	  * @Create Date: 2016-2-5下午2:53:27
	  * @Parameters: QuestionnaireController
	  * @Return: String
	 */
	@RequestMapping(value = "/updateAssessTime")
	public String updateAssessTime(
			@ModelAttribute("resultList") QuestionnaireValidCommond validcommond,
			HttpServletRequest request, HttpServletResponse response,
			@ModelAttribute("resultState") ResultState resultState)
					throws Exception
	{
		String r =questionnaireService.updateAssessTime(validcommond);
		if(r.equals("SUCESS")){
			resultState.setMessage("修改成功！");
			resultState.setState(ResultState.RESULT_STATE_SUCCESS);
		}else if(r.equals("FAILURE")){
			resultState.setMessage("修改失败！");
			resultState.setState(ResultState.RESULT_STATE_FAIL);
		}
		return "";
	}
	
	/**
	 * @更新评估开始时间和评估结束时间
	  * @Name: updateAssessTime
	  * @Description: @param validcommond
	  * @Description: @param request
	  * @Description: @param response
	  * @Description: @param resultState
	  * @Description: @return
	  * @Description: @throws Exception
	  * @Author: GUOLEI
	  * @Version: V1.00
	  * @Create Date: 2016-2-5下午2:53:27
	  * @Parameters: QuestionnaireController
	  * @Return: String
	 */
	@RequestMapping(value = "/updateCourseAssessTime")
	public String updateCourseAssessTime(
			@ModelAttribute("resultList") QuestionnaireValidCommond validcommond,
			HttpServletRequest request, HttpServletResponse response,
			@ModelAttribute("resultState") ResultState resultState)
					throws Exception
	{
		String r =questionnaireService.updateCourseAssessTime(validcommond);
		if(r.equals("SUCESS")){
			resultState.setMessage("修改成功！");
			resultState.setState(ResultState.RESULT_STATE_SUCCESS);
		}else if(r.equals("FAILURE")){
			resultState.setMessage("修改失败！");
			resultState.setState(ResultState.RESULT_STATE_FAIL);
		}
		return "";
	}
	
	/**
	 * @删除问卷
	  * @Name: updateAssessTime
	  * @Description: @param validcommond
	  * @Description: @param request
	  * @Description: @param response
	  * @Description: @param resultState
	  * @Description: @return
	  * @Description: @throws Exception
	  * @Author: GUOLEI
	  * @Version: V1.00
	  * @Create Date: 2016-2-5下午2:53:27
	  * @Parameters: QuestionnaireController
	  * @Return: String
	 */
	@RequestMapping(value = "/deleteQuestionnaires")
	public String deleteQuestionnaires(
			@ModelAttribute("resultList") QuestionnaireValidCommond validcommond,
			HttpServletRequest request, HttpServletResponse response,
			@ModelAttribute("resultState") ResultState resultState)
					throws Exception
	{
		String r =questionnaireService.deleteQuestionnaires(validcommond);
		if(r.equals("SUCESS")){
			resultState.setMessage("删除成功！");
			resultState.setState(ResultState.RESULT_STATE_SUCCESS);
		}else if(r.equals("FAILURE")){
			resultState.setMessage("删除失败！");
			resultState.setState(ResultState.RESULT_STATE_FAIL);
		}
		return "";
	}

	/**
	 * 查询课程汇总数据
	 * @Date 2016-09-27
	 * @author RongSL
	 * */
	@RequestMapping("/findClassCourseQuestionSummary")
	public void findClassCourseQuestionSummary(QuestionnaireQueryCommond commond,HttpServletRequest request, HttpServletResponse response,
			@ModelAttribute("resultState") ResultState resultState) throws Exception{
		
		try {
			// 第一步，创建一个webbook，对应一个Excel文件
			HSSFWorkbook wb = new HSSFWorkbook();
			// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
			// 设置字体样式
			HSSFFont font = wb.createFont();
			font.setFontName("黑体");
			font.setFontHeightInPoints((short) 18);// 设置字体大小
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示

			HSSFFont font2 = wb.createFont();
			font2.setFontName("楷体");
			font2.setFontHeightInPoints((short) 13);// 设置字体大小

			HSSFFont font3 = wb.createFont();// 题组样式
			font3.setFontName("微软雅黑");
			font3.setFontHeightInPoints((short) 15);// 设置字体大小

			// 设置单元格样式
			HSSFCellStyle style0 = wb.createCellStyle();// 标题样式
			style0.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 居中对齐
			style0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
			style0.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style0.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
			style0.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
			style0.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
			style0.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
			style0.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style0.setFillForegroundColor(HSSFColor.WHITE.index);// 单元格背景色
			style0.setFont(font);
			HSSFCellStyle style1 = wb.createCellStyle();// 单选样式
			style1.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 创建一个居中格式
			style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
			style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style1.setFillForegroundColor(HSSFColor.LIME.index);// 单元格背景色
			style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
			style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
			style1.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
			style1.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
			style1.setFont(font2);
			HSSFCellStyle style2 = wb.createCellStyle();// 多选样式
			style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 创建一个居中格式
			style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
			style2.setFillForegroundColor(HSSFColor.CORNFLOWER_BLUE.index);// 单元格背景色
			style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
			style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
			style2.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
			style2.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
			style2.setFont(font2);
			HSSFCellStyle style3 = wb.createCellStyle();
			style3.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 创建一个居中格式
			style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
			style3.setFillForegroundColor(HSSFColor.GOLD.index);// 单元格背景色
			style3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style3.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
			style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
			style3.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
			style3.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
			style3.setFont(font2);

			HSSFCellStyle style4 = wb.createCellStyle();
			style4.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
			style4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
			style4.setFillForegroundColor(HSSFColor.SKY_BLUE.index);// 单元格背景色
			style4.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style4.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
			style4.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
			style4.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
			style4.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
			style4.setFont(font3);
			
			HSSFSheet sheet=wb.createSheet("课程评估结果汇总表");
			HSSFRow row;
			HSSFCell cell;
			int column=0;
			int rows=0;
			for(int i=0;i<10;i++){
				sheet.setColumnWidth(i, 4*256*4);
			}
			sheet.setColumnWidth(1, 8*3*256);
			
			List<ClassQuestionnaireSummary> l=questionnaireService.findClassCourseQuestionSummary(commond);
			for(int i=0;i<l.size()+4;i++){
				sheet.createRow(i);
			}
			if(PropertyUtil.objectNotEmpty(l)){
			row=sheet.getRow(rows);
			sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 9));
			cell=row.createCell(column);
			cell.setCellValue("课程评估结果汇总表");
			cell.setCellStyle(style0);
			rows+=3;
			row=sheet.getRow(rows);
			cell=row.createCell(column);
			cell.setCellValue("序号");
			cell.setCellStyle(style4);
			column++;
			cell=row.createCell(column);
			cell.setCellValue("课程名称（代码）");
			cell.setCellStyle(style4);
			column++;
			cell=row.createCell(column);
			cell.setCellValue("授课教师");
			cell.setCellStyle(style4);
			column++;
			cell=row.createCell(column);
			cell.setCellValue("总体平均分");
			cell.setCellStyle(style4);
			column++;
			cell=row.createCell(column);
			cell.setCellValue("参评率");
			cell.setCellStyle(style4);
			column++;
			for(ClassQuestionnaireSummary c:l){
				for(String s:c.getQuestionNames()){
					cell=row.createCell(column);
					cell.setCellValue(s);
					cell.setCellStyle(style4);
					column++;
				}
				break;
			}
			for(ClassQuestionnaireSummary c:l){
				for(String s:c.getQuestionAnswers()){
					cell=row.createCell(column);
					cell.setCellValue(s);
					cell.setCellStyle(style4);
					column++;
				}
				break;
			}
			
			rows++;
			for(ClassQuestionnaireSummary c:l){
				column=0;
				row=sheet.getRow(rows);
				cell=row.createCell(column);
				cell.setCellValue(rows-3);
				cell.setCellStyle(style1);
				column++;
				cell=row.createCell(column);
				cell.setCellValue(c.getCourseName());
				if(c.getCourseName().length()*3*256>sheet.getColumnWidth(column)){
					sheet.setColumnWidth(column, c.getCourseName().length()*3*256);
				}
				cell.setCellStyle(style1);
				column++;
				cell=row.createCell(column);
				cell.setCellValue(c.getSpeaker());
				cell.setCellStyle(style1);
				column++;
				cell=row.createCell(column);
				cell.setCellValue(c.getAllAverage());
				cell.setCellStyle(style1);
				column++;
				cell=row.createCell(column);
//				cell.setCellValue(c.getQuestionnaireMethod().equals(TrainingClass.QUESTIONNAIRE_METHOD_TYPE_S)?c.getRecoveryRate():"-");
				cell.setCellValue(c.getRecoveryRate());
				cell.setCellStyle(style1);
				column++;
				rows++;
			}
			
			rows=4;
			for(ClassQuestionnaireSummary c:l){
				column=5;
				for(Double s:c.getAverages()){
					cell=sheet.getRow(rows).createCell(column);
					cell.setCellValue(s);
					cell.setCellStyle(style1);
					column++;
				}
				rows++;
			}
			
			rows=4;
			for(ClassQuestionnaireSummary c:l){
				row=sheet.getRow(rows);
				for(String s:c.getQuestionAnswerTexts()){
					column=9;
					cell=row.createCell(column);
					cell.setCellValue(s);
					cell.setCellStyle(style1);
					column++;
				}
				rows++;
			}
			
			response.reset();
			response.setContentType("application/x-msdownload");
			response.setHeader("Content-Disposition", "attachment; filename=" + new String(("课程评估结果汇总表").getBytes("gb2312"), "ISO-8859-1") + ".xls");
			ServletOutputStream outStream = null;
			outStream = response.getOutputStream();
			wb.write(outStream);
			outStream.close();
		} else {
			response.setCharacterEncoding("GB2312");
			PrintWriter pw = response.getWriter();
			pw.write("<script>alert('暂无数据');window.opener='';window.open('', '_self', '');window.close();</script>");
			pw.close();
		}
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	/**
	 * 查询培训班汇总数据
	 * @Date 2016-10-08
	 * @author RongSL
	 * */
	@RequestMapping("/findClassQuestionSummaryRemarks")
	public String findClassQuestionSummaryRemarks(QuestionnaireQueryCommond commond,HttpServletRequest request, HttpServletResponse response,
			@ModelAttribute("resultState") ResultState resultState) throws Exception{
		
		try {
			// 第一步，创建一个webbook，对应一个Excel文件
			HSSFWorkbook wb = new HSSFWorkbook();
			// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
			// 设置字体样式
			HSSFFont font = wb.createFont();
			font.setFontName("黑体");
			font.setFontHeightInPoints((short) 18);// 设置字体大小
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示

			HSSFFont font2 = wb.createFont();
			font2.setFontName("楷体");
			font2.setFontHeightInPoints((short) 13);// 设置字体大小

			HSSFFont font3 = wb.createFont();// 题组样式
			font3.setFontName("微软雅黑");
			font3.setFontHeightInPoints((short) 15);// 设置字体大小

			// 设置单元格样式
			HSSFCellStyle style0 = wb.createCellStyle();// 标题样式
			style0.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 居中对齐
			style0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
			style0.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style0.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
			style0.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
			style0.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
			style0.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
			style0.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style0.setFillForegroundColor(HSSFColor.WHITE.index);// 单元格背景色
			style0.setFont(font);
			HSSFCellStyle style1 = wb.createCellStyle();// 单选样式
			style1.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 居中对齐
			style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
			style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style1.setFillForegroundColor(HSSFColor.LIME.index);// 单元格背景色
			style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
			style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
			style1.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
			style1.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
			style1.setFont(font2);
			HSSFCellStyle style2 = wb.createCellStyle();// 多选样式
			style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 创建一个居中格式
			style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
			style2.setFillForegroundColor(HSSFColor.CORNFLOWER_BLUE.index);// 单元格背景色
			style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
			style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
			style2.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
			style2.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
			style2.setFont(font2);
			HSSFCellStyle style3 = wb.createCellStyle();
			style3.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 居中对齐
			style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
			style3.setFillForegroundColor(HSSFColor.GOLD.index);// 单元格背景色
			style3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style3.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
			style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
			style3.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
			style3.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
			style3.setFont(font2);

			HSSFCellStyle style4 = wb.createCellStyle();
			style4.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 居中对齐
			style4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
			style4.setFillForegroundColor(HSSFColor.SKY_BLUE.index);// 单元格背景色
			style4.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style4.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
			style4.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
			style4.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
			style4.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
			style4.setFont(font3);
			
			HSSFSheet sheet=wb.createSheet("培训班汇总表");
			HSSFCell cell;
			int column=0;
			int rows=0;
			int defaultColumn=8;//默认值
			if(PropertyUtil.objectNotEmpty(commond.getSearchClassIds())&&PropertyUtil.objectNotEmpty(commond.getSearchClassIds()[0])){
				commond.setSearchClassIds(commond.getSearchClassIds()[0].split(","));
			}
//			else{
//				response.setCharacterEncoding("GB2312");
//				PrintWriter pw = response.getWriter();
//				pw.write("<script>alert('暂无数据');window.opener='';window.open('', '_self', '');window.close();</script>");
//				pw.close();
//				return null;
//			}
			List<ClassQuestionnaireSummary> list=this.questionnaireService.findClassQuestionSummaryRemarks(commond);
			if(PropertyUtil.objectNotEmpty(list)&&PropertyUtil.objectNotEmpty(list.get(0))&&PropertyUtil.objectNotEmpty(list.get(0).getQuestionnaireName())){
				//初始化单元格
				for(int i=0;i<3+list.size();i++){
					sheet.createRow(i);
					for(int j=0;j<18;j++){
						sheet.getRow(i).createCell(j).setCellStyle(style4);
					}
				}
				for(int i=4;i<17;i++){
					sheet.setColumnWidth(i, 5*3*256);
				}
				for(int i=9;i<14;i++){
					sheet.setColumnWidth(i, 8*3*256);
				}
				sheet.setColumnWidth(0, 2*3*256);
				sheet.setColumnWidth(1, 5*3*256);
				sheet.setColumnWidth(6, 6*3*256);
				sheet.setColumnWidth(8, 9*3*256);
				sheet.setColumnWidth(16, 8*3*256);
				sheet.setColumnWidth(17, 8*3*256);
				
				sheet.addMergedRegion(new CellRangeAddress(rows, rows, column, 17));
				cell=sheet.getRow(rows).createCell(column);
				cell.setCellValue("培训班汇总表");
				cell.setCellStyle(style0);
				rows++;
				sheet.addMergedRegion(new CellRangeAddress(rows,rows+1,column,column));
				cell=sheet.getRow(rows).createCell(column);
				cell.setCellValue("序号");
				cell.setCellStyle(style4);
				column++;
				sheet.addMergedRegion(new CellRangeAddress(rows,rows+1,column,column));
				cell=sheet.getRow(rows).createCell(column);
				cell.setCellValue("年/月");
				cell.setCellStyle(style4);
				column++;
				sheet.addMergedRegion(new CellRangeAddress(rows, rows+1, column, column));
				cell=sheet.getRow(rows).createCell(column);
				cell.setCellValue("主办单位");
				cell.setCellStyle(style4);
				column++;
				sheet.addMergedRegion(new CellRangeAddress(rows, rows+1, column, column));
				cell=sheet.getRow(rows).createCell(column);
				cell.setCellValue("班名");
				cell.setCellStyle(style4);
				column++;
				sheet.addMergedRegion(new CellRangeAddress(rows, rows+1, column, column));
				cell=sheet.getRow(rows).createCell(column);
				cell.setCellValue("总体平均分");
				cell.setCellStyle(style4);
				column++;
//				sheet.addMergedRegion(new CellRangeAddress(rows, rows+1, column, column));
//				cell=sheet.getRow(rows).createCell(column);
//				cell.setCellValue("总分");
//				cell.setCellStyle(style4);
//				column++;
//				sheet.addMergedRegion(new CellRangeAddress(rows, rows+1, column, column));
//				cell=sheet.getRow(rows).createCell(column);
//				cell.setCellValue("实际参训人数");
//				cell.setCellStyle(style4);
//				column++;
				sheet.addMergedRegion(new CellRangeAddress(rows, rows+1, column, column));
				cell=sheet.getRow(rows).createCell(column);
				cell.setCellValue("参评人数");
				cell.setCellStyle(style4);
				column++;
				sheet.addMergedRegion(new CellRangeAddress(rows, rows+1, column, column));
				cell=sheet.getRow(rows).createCell(column);
				cell.setCellValue("报名通过人数");
				cell.setCellStyle(style4);
				column++;
				sheet.addMergedRegion(new CellRangeAddress(rows, rows+1, column, column));
				cell=sheet.getRow(rows).createCell(column);
				cell.setCellValue("参评率");
				cell.setCellStyle(style4);
				column++;
				sheet.addMergedRegion(new CellRangeAddress(rows, rows, column, column+2));
				cell=sheet.getRow(rows).createCell(column);
				cell.setCellValue("培训设计");
				cell.setCellStyle(style4);
				sheet.addMergedRegion(new CellRangeAddress(rows+1, rows+1, column, column));
				cell=sheet.getRow(rows+1).createCell(column);
				cell.setCellValue("与培训需求的适配度");
				cell.setCellStyle(style4);
				column++;
				sheet.addMergedRegion(new CellRangeAddress(rows+1, rows+1, column, column));
				cell=sheet.getRow(rows+1).createCell(column);
				cell.setCellValue("课程设计的科学性");
				cell.setCellStyle(style4);
				column++;
				sheet.addMergedRegion(new CellRangeAddress(rows+1, rows+1, column, column));
				cell=sheet.getRow(rows+1).createCell(column);
				cell.setCellValue("师资选配的合理性");
				cell.setCellStyle(style4);
				column++;
				sheet.addMergedRegion(new CellRangeAddress(rows, rows, column, column+2));
				cell=sheet.getRow(rows).createCell(column);
				cell.setCellValue("培训实施");
				cell.setCellStyle(style4);
				sheet.addMergedRegion(new CellRangeAddress(rows+1, rows+1, column, column));
				cell=sheet.getRow(rows+1).createCell(column);
				cell.setCellValue("教学内容的满意度");
				cell.setCellStyle(style4);
				column++;
				sheet.addMergedRegion(new CellRangeAddress(rows+1, rows+1, column, column));
				cell=sheet.getRow(rows+1).createCell(column);
				cell.setCellValue("教学方法的有效性");
				cell.setCellStyle(style4);
				column++;
				sheet.addMergedRegion(new CellRangeAddress(rows+1, rows+1, column, column));
				cell=sheet.getRow(rows+1).createCell(column);
				cell.setCellValue("教学组织的有序性");
				cell.setCellStyle(style4);
				column++;
				sheet.addMergedRegion(new CellRangeAddress(rows, rows, column, column+1));
				cell=sheet.getRow(rows).createCell(column);
				cell.setCellValue("培训管理");
				cell.setCellStyle(style4);
				sheet.addMergedRegion(new CellRangeAddress(rows+1, rows+1, column, column));
				cell=sheet.getRow(rows+1).createCell(column);
				cell.setCellValue("学风情况");
				cell.setCellStyle(style4);
				column++;
				sheet.addMergedRegion(new CellRangeAddress(rows+1, rows+1, column, column));
				cell=sheet.getRow(rows+1).createCell(column);
				cell.setCellValue("后勤保障");
				cell.setCellStyle(style4);
				column++;
				sheet.addMergedRegion(new CellRangeAddress(rows, rows, column, column+1));
				cell=sheet.getRow(rows).createCell(column);
				cell.setCellValue("培训效果");
				cell.setCellStyle(style4);
				sheet.addMergedRegion(new CellRangeAddress(rows+1, rows+1, column, column));
				cell=sheet.getRow(rows+1).createCell(column);
				cell.setCellValue("对实际工作的帮助");
				cell.setCellStyle(style4);
				column++;
				sheet.addMergedRegion(new CellRangeAddress(rows+1, rows+1, column, column));
				cell=sheet.getRow(rows+1).createCell(column);
				cell.setCellValue("对能力素养的提高");
				cell.setCellStyle(style4);
				column++;
				rows++;
				
				//遍历数据
				for(ClassQuestionnaireSummary c:list){
					column=0;
					rows++;
					for(int i=0;i<18;i++){
						cell=sheet.getRow(rows).createCell(column);
						switch(column){
							case 0:
								cell.setCellValue(rows-2);
								break;
							case 1:
								cell.setCellValue(c.getStartDate());
								break;
							case 2:
								cell.setCellValue(c.getSponsorName());
								if(sheet.getColumnWidth(column)<(c.getSponsorName().length()*3*256)){
									sheet.setColumnWidth(column, c.getSponsorName().length()*3*256);
								}
								break;
							case 3:
								cell.setCellValue(c.getClassName());
								if(sheet.getColumnWidth(column)<(c.getClassName().length()*3*256)){
									sheet.setColumnWidth(column, c.getClassName().length()*3*256);
								}
								break;
							case 4:
								cell.setCellValue(c.getAllAverage());
								break;
//							case 5:
//								cell.setCellValue(c.getCount());
//								break;
//							case 5:
//								cell.setCellValue(c.getTraiCount());
//								break;
							case 5:
								cell.setCellValue(c.getAssCount());
								break;
							case 6:
								cell.setCellValue(c.getUserCount().toString());
								break;
							case 7:
								cell.setCellValue(c.getAssCountRate());
//								cell.setCellValue(c.getQuestionnaireMethod().equals(TrainingClass.QUESTIONNAIRE_METHOD_TYPE_S)?c.getAssCountRate():"-");
								break;
							case 8:
								cell.setCellValue(c.getAverages().get(column-defaultColumn));
								break;
							case 9:
								cell.setCellValue(c.getAverages().get(column-defaultColumn));
								break;
							case 10:
								cell.setCellValue(c.getAverages().get(column-defaultColumn));
								break;
							case 11:
								cell.setCellValue(c.getAverages().get(column-defaultColumn));
								break;
							case 12:
								cell.setCellValue(c.getAverages().get(column-defaultColumn));
								break;
							case 13:
								cell.setCellValue(c.getAverages().get(column-defaultColumn));
								break;
							case 14:
								cell.setCellValue(c.getAverages().get(column-defaultColumn));
								break;
							case 15:
								cell.setCellValue(c.getAverages().get(column-defaultColumn));
								break;
							case 16:
								cell.setCellValue(c.getAverages().get(column-defaultColumn));
								break;
							case 17:
								cell.setCellValue(c.getAverages().get(column-defaultColumn));
								break;
						}
						cell.setCellStyle(style3);
						column++;
					}
				}
//				for(int i=0;i<9;i++){
//					sheet.setColumnWidth(i, 4*256*4);
//				}
//				sheet.setColumnWidth(1, 4*256*6);
				
				response.reset();
				response.setContentType("application/x-msdownload");
				response.setHeader("Content-Disposition", "attachment; filename=" + new String(("培训班汇总表").getBytes("gb2312"), "ISO-8859-1") + ".xls");
				ServletOutputStream outStream = null;
				outStream = response.getOutputStream();
				wb.write(outStream);
				outStream.close();
			} else {
				response.setCharacterEncoding("GB2312");
				PrintWriter pw = response.getWriter();
				pw.write("<script>alert('暂无数据');window.opener='';window.open('', '_self', '');window.close();</script>");
				pw.close();
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
}
